import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from django.http import HttpResponse
from django.utils import timezone
from io import BytesIO
import pandas as pd
from datetime import datetime
import re


class ExcelExporter:
    """Excel导出工具类"""
    
    def __init__(self):
        self.workbook = openpyxl.Workbook()
        self.worksheet = self.workbook.active
        
    def set_header_style(self):
        """设置表头样式"""
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        for cell in self.worksheet[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = border
    
    def set_data_style(self, start_row=2):
        """设置数据行样式"""
        border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        alignment = Alignment(horizontal="left", vertical="center")
        
        for row in self.worksheet.iter_rows(min_row=start_row):
            for cell in row:
                if cell.value is not None:
                    cell.border = border
                    cell.alignment = alignment
    
    def auto_adjust_column_width(self):
        """自动调整列宽"""
        for column in self.worksheet.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)
            
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            
            adjusted_width = min(max_length + 2, 50)
            self.worksheet.column_dimensions[column_letter].width = adjusted_width
    
    def export_projects(self, projects):
        """导出项目数据"""
        self.worksheet.title = "项目列表"
        
        # 设置表头
        headers = [
            '项目名称', '项目描述', '学期', '教务负责人', 
            '项目经费', '已使用经费', '剩余经费', '经费使用率',
            '子项目数量', '创建时间', '更新时间'
        ]
        
        for col, header in enumerate(headers, 1):
            self.worksheet.cell(row=1, column=col, value=header)
        
        # 填充数据
        for row, project in enumerate(projects, 2):
            self.worksheet.cell(row=row, column=1, value=project.name)
            self.worksheet.cell(row=row, column=2, value=project.description)
            self.worksheet.cell(row=row, column=3, value=project.semester)
            self.worksheet.cell(row=row, column=4, value=project.admin_manager.get_full_name() or project.admin_manager.username)
            self.worksheet.cell(row=row, column=5, value=float(project.budget))
            self.worksheet.cell(row=row, column=6, value=float(project.used_budget))
            self.worksheet.cell(row=row, column=7, value=float(project.remaining_budget))
            self.worksheet.cell(row=row, column=8, value=f"{project.budget_usage_percentage:.1f}%")
            self.worksheet.cell(row=row, column=9, value=project.subproject_count)
            self.worksheet.cell(row=row, column=10, value=project.created_at.strftime('%Y-%m-%d %H:%M'))
            self.worksheet.cell(row=row, column=11, value=project.updated_at.strftime('%Y-%m-%d %H:%M'))
        
        self.set_header_style()
        self.set_data_style()
        self.auto_adjust_column_width()
        
        return self.workbook
    
    def export_subprojects(self, subprojects):
        """导出子项目数据"""
        self.worksheet.title = "子项目列表"
        
        # 设置表头
        headers = [
            '子项目名称', '子项目描述', '所属项目', '负责人', 
            '项目状态', '子项目预算', '已使用预算', '剩余预算', 
            '预算使用率', '创建人', '创建时间', '更新时间'
        ]
        
        for col, header in enumerate(headers, 1):
            self.worksheet.cell(row=1, column=col, value=header)
        
        # 填充数据
        for row, subproject in enumerate(subprojects, 2):
            self.worksheet.cell(row=row, column=1, value=subproject.name)
            self.worksheet.cell(row=row, column=2, value=subproject.description)
            self.worksheet.cell(row=row, column=3, value=subproject.project.name)
            self.worksheet.cell(row=row, column=4, value=subproject.responsible_person.get_full_name() or subproject.responsible_person.username)
            self.worksheet.cell(row=row, column=5, value=subproject.get_status_display())
            self.worksheet.cell(row=row, column=6, value=float(subproject.budget))
            self.worksheet.cell(row=row, column=7, value=float(subproject.used_budget))
            self.worksheet.cell(row=row, column=8, value=float(subproject.remaining_budget))
            self.worksheet.cell(row=row, column=9, value=f"{subproject.budget_usage_percentage:.1f}%")
            self.worksheet.cell(row=row, column=10, value=subproject.created_by.get_full_name() or subproject.created_by.username)
            self.worksheet.cell(row=row, column=11, value=subproject.created_at.strftime('%Y-%m-%d %H:%M'))
            self.worksheet.cell(row=row, column=12, value=subproject.updated_at.strftime('%Y-%m-%d %H:%M'))
        
        self.set_header_style()
        self.set_data_style()
        self.auto_adjust_column_width()
        
        return self.workbook
    
    def create_import_template(self, template_type='project'):
        """创建导入模板"""
        if template_type == 'project':
            self.worksheet.title = "项目导入模板"
            headers = ['项目名称*', '项目描述*', '学期*', '教务负责人用户名*', '项目经费*']
            sample_data = [
                '示例项目1', '这是一个示例项目描述', '2024-2025-1', 'admin', '100000.00'
            ]
        else:  # subproject
            self.worksheet.title = "子项目导入模板"
            headers = ['子项目名称*', '子项目描述*', '所属项目名称*', '负责人用户名*', '子项目预算*', '项目状态']
            sample_data = [
                '示例子项目1', '这是一个示例子项目描述', '示例项目1', 'user1', '50000.00', '初始'
            ]
        
        # 设置表头
        for col, header in enumerate(headers, 1):
            self.worksheet.cell(row=1, column=col, value=header)
        
        # 添加示例数据
        for col, data in enumerate(sample_data, 1):
            self.worksheet.cell(row=2, column=col, value=data)
        
        # 添加说明
        note_row = len(headers) + 4
        self.worksheet.cell(row=note_row, column=1, value="导入说明：")
        self.worksheet.cell(row=note_row + 1, column=1, value="1. 带*号的字段为必填项")
        self.worksheet.cell(row=note_row + 2, column=1, value="2. 学期格式：YYYY-YYYY-N (如: 2024-2025-1)")
        self.worksheet.cell(row=note_row + 3, column=1, value="3. 用户名必须是系统中已存在的用户")
        self.worksheet.cell(row=note_row + 4, column=1, value="4. 预算金额请使用数字格式")
        if template_type == 'subproject':
            self.worksheet.cell(row=note_row + 5, column=1, value="5. 项目状态可选：初始、立项通过、中期检查通过、结项完成")
        
        self.set_header_style()
        self.auto_adjust_column_width()
        
        return self.workbook


class ExcelImporter:
    """Excel导入工具类"""
    
    def __init__(self, file_content):
        self.file_content = file_content
        self.errors = []
        self.success_count = 0
        
    def validate_semester_format(self, semester):
        """验证学期格式"""
        if not semester:
            return False
        return bool(re.match(r'^\d{4}-\d{4}-[12]$', str(semester)))
    
    def import_projects(self):
        """导入项目数据"""
        from apps.projects.models import Project
        from django.contrib.auth.models import User
        
        try:
            df = pd.read_excel(BytesIO(self.file_content))
            
            # 检查必需的列
            required_columns = ['项目名称', '项目描述', '学期', '教务负责人用户名', '项目经费']
            missing_columns = [col for col in required_columns if col not in df.columns]
            
            if missing_columns:
                self.errors.append(f"缺少必需的列: {', '.join(missing_columns)}")
                return False
            
            for index, row in df.iterrows():
                try:
                    # 验证必填字段
                    if pd.isna(row['项目名称']) or not str(row['项目名称']).strip():
                        self.errors.append(f"第{index + 2}行：项目名称不能为空")
                        continue
                    
                    if pd.isna(row['学期']) or not self.validate_semester_format(row['学期']):
                        self.errors.append(f"第{index + 2}行：学期格式错误，应为 YYYY-YYYY-N")
                        continue
                    
                    if pd.isna(row['项目经费']) or float(row['项目经费']) <= 0:
                        self.errors.append(f"第{index + 2}行：项目经费必须大于0")
                        continue
                    
                    # 查找教务负责人
                    try:
                        admin_manager = User.objects.get(username=row['教务负责人用户名'])
                    except User.DoesNotExist:
                        self.errors.append(f"第{index + 2}行：用户 '{row['教务负责人用户名']}' 不存在")
                        continue
                    
                    # 检查项目是否已存在
                    if Project.objects.filter(name=row['项目名称'], semester=row['学期']).exists():
                        self.errors.append(f"第{index + 2}行：项目 '{row['项目名称']}' 在学期 '{row['学期']}' 已存在")
                        continue
                    
                    # 创建项目
                    project = Project.objects.create(
                        name=str(row['项目名称']).strip(),
                        description=str(row['项目描述']).strip() if not pd.isna(row['项目描述']) else '',
                        semester=str(row['学期']).strip(),
                        admin_manager=admin_manager,
                        budget=float(row['项目经费'])
                    )
                    
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"第{index + 2}行：{str(e)}")
                    continue
            
            return True
            
        except Exception as e:
            self.errors.append(f"文件读取错误：{str(e)}")
            return False
    
    def import_subprojects(self):
        """导入子项目数据"""
        from apps.subprojects.models import SubProject
        from apps.projects.models import Project
        from django.contrib.auth.models import User
        
        try:
            df = pd.read_excel(BytesIO(self.file_content))
            
            # 检查必需的列
            required_columns = ['子项目名称', '子项目描述', '所属项目名称', '负责人用户名', '子项目预算']
            missing_columns = [col for col in required_columns if col not in df.columns]
            
            if missing_columns:
                self.errors.append(f"缺少必需的列: {', '.join(missing_columns)}")
                return False
            
            # 状态映射
            status_mapping = {
                '初始': 'initial',
                '立项通过': 'approved',
                '中期检查通过': 'mid_check_passed',
                '结项完成': 'completed'
            }
            
            for index, row in df.iterrows():
                try:
                    # 验证必填字段
                    if pd.isna(row['子项目名称']) or not str(row['子项目名称']).strip():
                        self.errors.append(f"第{index + 2}行：子项目名称不能为空")
                        continue
                    
                    if pd.isna(row['子项目预算']) or float(row['子项目预算']) <= 0:
                        self.errors.append(f"第{index + 2}行：子项目预算必须大于0")
                        continue
                    
                    # 查找所属项目
                    try:
                        project = Project.objects.get(name=row['所属项目名称'])
                    except Project.DoesNotExist:
                        self.errors.append(f"第{index + 2}行：项目 '{row['所属项目名称']}' 不存在")
                        continue
                    
                    # 查找负责人
                    try:
                        responsible_person = User.objects.get(username=row['负责人用户名'])
                    except User.DoesNotExist:
                        self.errors.append(f"第{index + 2}行：用户 '{row['负责人用户名']}' 不存在")
                        continue
                    
                    # 检查预算是否超出项目剩余预算
                    budget = float(row['子项目预算'])
                    if not project.can_add_subproject_budget(budget):
                        self.errors.append(f"第{index + 2}行：预算超出项目剩余预算 (剩余: ¥{project.remaining_budget:.2f})")
                        continue
                    
                    # 处理状态
                    status = 'initial'  # 默认状态
                    if not pd.isna(row.get('项目状态')):
                        status_display = str(row['项目状态']).strip()
                        if status_display in status_mapping:
                            status = status_mapping[status_display]
                        else:
                            self.errors.append(f"第{index + 2}行：无效的项目状态 '{status_display}'")
                            continue
                    
                    # 创建子项目
                    subproject = SubProject.objects.create(
                        name=str(row['子项目名称']).strip(),
                        description=str(row['子项目描述']).strip() if not pd.isna(row['子项目描述']) else '',
                        project=project,
                        responsible_person=responsible_person,
                        budget=budget,
                        status=status,
                        created_by=responsible_person  # 默认创建人为负责人
                    )
                    
                    self.success_count += 1
                    
                except Exception as e:
                    self.errors.append(f"第{index + 2}行：{str(e)}")
                    continue
            
            return True
            
        except Exception as e:
            self.errors.append(f"文件读取错误：{str(e)}")
            return False